
CREATE TABLE wos_npl_match
SELECT
  w.wosid,
  w.pubtype,
  w.doctype,
  w.journal,
  w.journal_abbr,
  w.journal_abbr_iso,
  w.authors,
  w.affiliations,
  w.biblio,
  w.YEAR wosyear,
  w.volume,
  w.page,
  w.title,
  n.cited,
  n.citing,
  YEAR(m.appdate) appyear,
  YEAR(m.grantdate) grantyear,
  IF(length(w.YEAR) > 0, locate(w.YEAR, n.cited), 0) in_year,
  IF(length(w.volume) > 0, locate(w.volume, n.cited), 0) in_volume,
  IF(length(w.page) > 0, locate(w.page, n.cited), 0) in_page,
  IF(length(w.journal) > 0, locate(w.journal, n.cited), 0) in_journal,
  IF(length(w.journal_abbr) > 0, locate(w.journal_abbr, n.cited), 0) in_journal_abbr,
  IF(length(w.journal_abbr_iso) > 0, locate(w.journal_abbr_iso, n.cited), 0) in_journal_iso,
  LENGTH(w.title) title_len,
  IF(
      LENGTH(w.title)<20
      AND
        w.title NOT IN ('gene-therapy','genetherapy','gene therapy','interleukin','electrons in glass')
      AND
        (
          IF(length(w.volume) > 0, locate(w.volume, n.cited), 0) = 0 OR
          IF(length(w.page) > 0, locate(w.page, n.cited), 0) = 0
        )
      AND
        (
          NOT (locate('mass-spectrometry', w.cited)>0 AND locate('burlingame', w.cited)>0) AND
          NOT (locate('soft lithogrpahy', w.cited)>0 AND locate('xia', w.cited)>0) AND
          NOT (locate('tissue engineering', w.cited)>0 AND locate('langer', w.cited)>0) AND
          NOT (locate('tumor angiogenesis', w.cited)>0 AND locate('folkman', w.cited)>0) AND
          NOT (locate('negative refraction', w.cited)>0 AND locate('pendry', w.cited)>0) AND
          NOT (locate('moire topography', w.cited)>0 AND locate('takasaki', w.cited)>0) AND
          NOT (locate('gateways to clinical trials', w.cited)>0 AND locate('bayes', w.cited)>0) AND
          NOT (locate('genebank', w.cited)>0 AND locate('benson', w.cited)>0)
        )
      OR
      (
        locate('trasplantation', w.title)>0 OR
        locate('from the internet', w.title)>0 OR
        locate('scientific american', w.title)>0 OR
        locate('instrumentation', w.title)>0 OR
        locate('artificial intelligence', w.title)>0 OR
        locate('artificial-intelligence', w.title)>0 OR
        locate('letter to the editor', w.title)>0 OR
        locate('in the pipeline', w.title)>0 OR
        locate('patent publication', w.title)>0 OR
        locate('standardization', w.title)>0
      )
      OR w.title='introduction'
      OR w.title='abstract'
      OR w.title='abstracts'
    ,1,0) dropx
FROM
  wos w 
  INNER JOIN
    wos_npl n 
    ON w.wosid = n.wosid 
  INNER JOIN
    uspto_meta m 
    ON m.patno = n.citing 
;


